package ticketImplment;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import JDBCUtil.JDBCUtil;
import routeDao.RouteDao;
import routeEntity.Route;
import ticketDao.TicketDao;
import ticketEntity.Ticket;
import userEntity.User;

public class TicketImplment implements TicketDao{
@Override
	//用户买票，传入一个用户实体对象Uname和一个路线实体对象Rname，
	//在数据库中添加一条记录(该记录的字段来源于两个实体对象) 成功则返回true
	public boolean AddTicket(User user,Route route) {
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql ="insert into ticket(Uname,idCard,Rname,fromStation,toStation,fromTime,toTime) values (?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,user.getUname());
			ps.setString(2,user.getIdCard());
			ps.setString(3,route.getRname());
			ps.setString(4,route.getFromStation());
			ps.setString(5,route.getToStation());
			ps.setTimestamp(6,route.getFromTime());
			ps.setTimestamp(7,route.getToTime());
			
			//执行语句
			ps.executeUpdate();//rs=ps.executeQuery();
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		return false;
		
	}

@Override
	//根据用户名查询指定用户买的票 ，传入用户名Uname，返回List
	public List<Ticket> QueryTicketUser(String Uname) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql="select * from ticket where Uname=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1,Uname);
			//执行语句
			rs=ps.executeQuery();
			//创建一个集合
			List<Ticket> list=new ArrayList<Ticket>();
			while(rs.next()) {
				Ticket ticket=new Ticket();
				ticket.setTid(rs.getInt("Tid"));
				ticket.setUname(rs.getString("Uname"));
				ticket.setIdCard(rs.getString("idCard"));
				ticket.setRname(rs.getString("Rname"));
				ticket.setFromStation(rs.getString("fromStation"));
				ticket.setToStation(rs.getString("toStation"));
				ticket.setFromTime(rs.getTimestamp("fromTime"));
				ticket.setToTime(rs.getTimestamp("toTime"));
				list.add(ticket);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		return null;
	}

@Override
	//查询全部卖出的票，被调用时，返回List
	public List<Ticket> QueryTicket() {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql="select * from ticket";
			ps=conn.prepareStatement(sql);
			//执行语句
			rs=ps.executeQuery();
			//创建一个集合
			List<Ticket> list=new ArrayList<Ticket>();
			while(rs.next()) {
				Ticket ticket=new Ticket();
				ticket.setTid(rs.getInt("Tid"));
				ticket.setUname(rs.getString("Uname"));
				ticket.setIdCard(rs.getString("idCard"));
				ticket.setRname(rs.getString("Rname"));
				ticket.setFromStation(rs.getString("fromStation"));
				ticket.setToStation(rs.getString("toStation"));
				ticket.setFromTime(rs.getTimestamp("fromTime"));
				ticket.setToTime(rs.getTimestamp("toTime"));
				list.add(ticket);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		return null;
	}
@Override
	public boolean DeleteTicket(int Tid) {
		// TODO Auto-generated method stub
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql="delete from ticket where Tid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,Tid);
			//执行语句
			ps.executeUpdate();//rs=ps.executeQuery();
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		return false;
	}

@Override
public List<Ticket> QueryTicketRname(String Rname) {
	// TODO Auto-generated method stub
	Connection conn=null;
	PreparedStatement ps=null;
	ResultSet rs =null;
	
	try {
		//创建连接
		conn=JDBCUtil.getConn();
		//取得相应的记录
		String sql="select * from ticket where Rname=?";
		ps=conn.prepareStatement(sql);
		ps.setString(1,Rname);
		//执行语句
		rs=ps.executeQuery();
		//创建一个集合
		List<Ticket> list=new ArrayList<Ticket>();
		while(rs.next()) {
			Ticket ticket=new Ticket();
			ticket.setTid(rs.getInt("Tid"));
			ticket.setUname(rs.getString("Uname"));
			ticket.setIdCard(rs.getString("idCard"));
			ticket.setRname(rs.getString("Rname"));
			ticket.setFromStation(rs.getString("fromStation"));
			ticket.setToStation(rs.getString("toStation"));
			ticket.setFromTime(rs.getTimestamp("fromTime"));
			ticket.setToTime(rs.getTimestamp("toTime"));
			list.add(ticket);
		}
		return list;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		//释放资源
		JDBCUtil.Close(conn, ps, rs);
	}
	return null;
}

@Override
public Ticket QueryTicketTid(int Tid) {
	// TODO Auto-generated method stub
	Connection conn=null;
	PreparedStatement ps=null;
	ResultSet rs =null;
	
	try {
		//创建连接
		conn=JDBCUtil.getConn();
		//取得相应的记录
		String sql="select * from ticket where Tid=?";
		ps=conn.prepareStatement(sql);
		ps.setInt(1,Tid);
		//执行语句
		rs=ps.executeQuery();
		//创建一个Ticket对象
		if(rs.next()) {
			Ticket ticket=new Ticket();
			ticket.setTid(rs.getInt("Tid"));
			ticket.setUname(rs.getString("Uname"));
			ticket.setIdCard(rs.getString("idCard"));
			ticket.setRname(rs.getString("Rname"));
			ticket.setFromStation(rs.getString("fromStation"));
			ticket.setToStation(rs.getString("toStation"));
			ticket.setFromTime(rs.getTimestamp("fromTime"));
			ticket.setToTime(rs.getTimestamp("toTime"));
			return ticket;
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		//释放资源
		JDBCUtil.Close(conn, ps, rs);
	}
	return null;
}
}
